package com.authine.cloudpivot.ext.utils;

import com.authine.cloudpivot.ext.dto.HistogramDataDto;
import com.authine.cloudpivot.ext.dto.HistogramDataVo;
import com.authine.cloudpivot.ext.dto.QueryIndexKanBanVo;
import com.authine.cloudpivot.ext.dto.kanban.KanbanDto;
import com.authine.cloudpivot.ext.dto.kanban.OperationKanbanQueryDto;
import com.authine.mvp.app.launcher.domain.utills.ApplicationContextUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import java.math.BigDecimal;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

@Slf4j
public class KanbanJdbcUtil {

    public static final String TODAY = "today";
    public static final String YESTERDAY = "yesterday";
    public static final String ALL = "all";
    public static final String OTHERDATE = "other";

    private static JdbcTemplate jdbcTemplate = ApplicationContextUtils.getBean(JdbcTemplate.class);

    public static List<HistogramDataVo> queryInquiryByTime(HistogramDataDto histogramDataDto) {
        StringBuffer sb = new StringBuffer();
        sb.append("select COUNT(1) inquiryCount, DATE_FORMAT(createdTime, '%m/%d') time " );
        sb.append(" from  sourcingcenter.i_sourcingcenter_t_inquiry_goods ");
        sb.append(" where deleted =0 ");
        sb.append(" and createdTime >='"+PmUtil.dateFormatStrYYYYMMddhhmmss(histogramDataDto.getStartTime())+"'");
        sb.append(" and createdTime <='"+PmUtil.dateFormatStrYYYYMMddhhmmss(histogramDataDto.getEndTime())+"'");
        sb.append(" and inquiry_goods_status in (3,4,5)");
        if(StringUtils.isNotBlank(histogramDataDto.getOrgName())){
            sb.append(" and organization_company_name like concat('%','").append(histogramDataDto.getOrgName()).append("','%') ");
        }
        sb.append(" GROUP BY DATE_FORMAT(createdTime, '%Y-%m-%d')");
        List<HistogramDataVo> kanbas = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(HistogramDataVo.class));
        return kanbas;
    }

    public static List<HistogramDataVo> queryDingbiaoByTime(HistogramDataDto histogramDataDto) {

        StringBuffer sb = new StringBuffer();
        sb.append("select COUNT(1) dingbiaoCount, DATE_FORMAT(createdTime, '%m/%d') time ");
        sb.append(" from  commoditymaterials.i_commoditymaterials_t_goods_purchase ");
        sb.append(" where  deleted=0 ");
        sb.append(" and createdTime >='" + PmUtil.dateFormatStrYYYYMMddhhmmss(histogramDataDto.getStartTime()) + "'");
        sb.append(" and createdTime <='" + PmUtil.dateFormatStrYYYYMMddhhmmss(histogramDataDto.getEndTime()) + "'");
        if (StringUtils.isNotBlank(histogramDataDto.getOrgName())) {
            sb.append(" and vendor_name like concat('%','").append(histogramDataDto.getOrgName()).append("','%') ");
        }
        sb.append(" GROUP BY DATE_FORMAT(createdTime, '%Y-%m-%d')");
        List<HistogramDataVo> kanbas = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(HistogramDataVo.class));
        return kanbas;
    }



        public static List<KanbanDto> queryInquiryByTime(OperationKanbanQueryDto kanban) {
        StringBuffer sb = new StringBuffer();
        sb.append("select COUNT(1) count, DATE_FORMAT(createdTime, '%Y-%m-%d') time " );
        sb.append(" from  sourcingcenter.i_sourcingcenter_t_inquiry_goods ");
        sb.append(" where deleted =0 ");
        sb.append(" and DATE_FORMAT(createdTime, '%Y-%m-%d')>='"+kanban.getStartTime()+"'");
        sb.append(" and DATE_FORMAT(createdTime, '%Y-%m-%d')<='"+kanban.getEndTime()+"'");
        sb.append(" and inquiry_goods_status in (3,4,5)");
        if(StringUtils.isNotBlank(kanban.getOrgName())){
            sb.append(" and organization_company_name like concat('%','").append(kanban.getOrgName()).append("','%') ");
        }
        sb.append(" GROUP BY DATE_FORMAT(createdTime, '%Y-%m-%d') ");
        sb.append(" union all");
        sb.append(" select COUNT(1) count, 'all' time  " );
        sb.append(" from  sourcingcenter.i_sourcingcenter_t_inquiry_goods ");
        sb.append(" where deleted =0 ");
        if(StringUtils.isNotBlank(kanban.getOrgName())){
            sb.append(" and organization_company_name like concat('%','").append(kanban.getOrgName()).append("','%') ");
        }
        sb.append(" and inquiry_goods_status in (3,4,5)");
        List<KanbanDto> kanbas = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<KanbanDto>(KanbanDto.class));
        return kanbas;
    }

    private static void extractedTime(KanbanDto item) {
        item.setDayTime(item.getTime());
        String time = getTimeString(item.getTime());
        item.setTime(time);
        if(StringUtils.isBlank(item.getCount())){
            item.setCount("0");
        };
        if(item.getTotal() == null){
            item.setTotal(new BigDecimal("0"));
        };

    }

    private static String getTimeString(String time) {
        Date d = new Date();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        String today = sdf.format(d);

        Calendar cal   =   Calendar.getInstance();
        cal.add(Calendar.DATE,   -1);
        String yesterday = new SimpleDateFormat( "yyyy-MM-dd").format(cal.getTime());


        if (today.equals(time)){
            return TODAY;
        }else if (ALL.equals(time)){
            return ALL;
        }else if (yesterday.equals(time)){
            return YESTERDAY;
        }else{
            return OTHERDATE;
        }
    }

    public static List<String> getDays(String startTime, String endTime) {

        // 返回的日期集合
        List<String> days = new ArrayList<String>();

        DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
        try {
            Date start = dateFormat.parse(startTime);
            Date end = dateFormat.parse(endTime);

            Calendar tempStart = Calendar.getInstance();
            tempStart.setTime(start);

            Calendar tempEnd = Calendar.getInstance();
            tempEnd.setTime(end);
            tempEnd.add(Calendar.DATE, +1);// 日期加1(包含结束)
            while (tempStart.before(tempEnd)) {
                days.add(dateFormat.format(tempStart.getTime()));
                tempStart.add(Calendar.DAY_OF_YEAR, 1);
            }

        } catch (ParseException  e) {
            e.printStackTrace();
        }

        return days;
    }
    public static List<QueryIndexKanBanVo> queryInquiryByTimeToday(String orgName) {
        StringBuffer sb = new StringBuffer();
        sb.append("select COUNT(1) count, 'toDayInquiryGoodsTimes' name " );
        sb.append(" from  sourcingcenter.i_sourcingcenter_t_inquiry_goods ");
        sb.append(" where deleted =0 ");
        sb.append(" and createdTime >='"+PmUtil.getStartDateStr(Calendar.DATE,0)+"'");
        sb.append(" and createdTime <='"+PmUtil.getEndDateStr(Calendar.DATE,0)+"'");
        sb.append(" and inquiry_goods_status in (3,4,5)");
        if(StringUtils.isNotBlank(orgName)){
            sb.append(" and organization_company_name like concat('%','").append(orgName).append("','%') ");
        }
        List<QueryIndexKanBanVo> kanbas = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(QueryIndexKanBanVo.class));
        return kanbas;
    }

    public static List<QueryIndexKanBanVo> queryDingbiaoByTimeToday(String orgName) {

        StringBuffer sb = new StringBuffer();
        sb.append("select COUNT(1) count, 'todayDingbiaoTimes' name " );
        sb.append(" from  commoditymaterials.i_commoditymaterials_t_goods_purchase ");
        sb.append(" where  deleted=0 ");
        sb.append(" and createdTime >='"+PmUtil.getStartDateStr(Calendar.DATE,0)+"'");
        sb.append(" and createdTime <='"+PmUtil.getEndDateStr(Calendar.DATE,0)+"'");
        if (StringUtils.isNotBlank(orgName)){
            sb.append(" and vendor_name like concat('%','").append(orgName).append("','%') ");
        }
        List<QueryIndexKanBanVo> kanbas = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(QueryIndexKanBanVo.class));
        return kanbas;

    }

    public static List<QueryIndexKanBanVo> queryQuotationByTimeYesterday(String  orgName) {
        StringBuffer sb = new StringBuffer();
        sb.append("select COUNT(1) count, 'yesterdayInquiryGoodsTimes' name " );
        sb.append(" from  sourcingcenter.i_sourcingcenter_t_inquiry_goods ");
        sb.append(" where deleted =0 ");
        sb.append(" and createdTime >='"+PmUtil.getStartDateStr(Calendar.DATE,-1)+"'");
        sb.append(" and createdTime <='"+PmUtil.getEndDateStr(Calendar.DATE,-1)+"'");
        sb.append(" and inquiry_goods_status in (3,4,5)");
        if(StringUtils.isNotBlank(orgName)){
            sb.append(" and organization_company_name like concat('%','").append(orgName).append("','%') ");
        }
        List<QueryIndexKanBanVo> kanbas = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(QueryIndexKanBanVo.class));
        return kanbas;

    }


    public static List<QueryIndexKanBanVo> queryQuotationByYesterday(String orgName) {
        StringBuffer sb = new StringBuffer();
        sb.append("select COUNT(1) count, 'yesterdayQuotationTimes' name " );
        sb.append(" from  sourcingcenter.i_sourcingcenter_t_quotation_goods ");
        sb.append(" where deleted = 0");
        sb.append(" and createdTime >='"+PmUtil.getStartDateStr(Calendar.DATE,-1)+"'");
        sb.append(" and createdTime <='"+PmUtil.getEndDateStr(Calendar.DATE,-1)+"'");
        sb.append(" and quotation_goods_status in (3,4,5,6)");
        if (StringUtils.isNotBlank(orgName)){
            sb.append(" and vendor_organization_name like concat('%','").append(orgName).append("','%') ");
        }
        List<QueryIndexKanBanVo> kanbas = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(QueryIndexKanBanVo.class));
        return kanbas;

    }

    public static List<KanbanDto> queryQuotationByTime(OperationKanbanQueryDto kanban) {
        StringBuffer sb = new StringBuffer();
        sb.append("select COUNT(1) count, DATE_FORMAT(createdTime, '%Y-%m-%d') time " );
        sb.append(" from  sourcingcenter.i_sourcingcenter_t_quotation_goods ");
        sb.append(" where deleted = 0");
        sb.append(" and DATE_FORMAT(createdTime, '%Y-%m-%d')>='"+kanban.getStartTime()+"'");
        sb.append(" and DATE_FORMAT(createdTime, '%Y-%m-%d')<='"+kanban.getEndTime()+"'");
        sb.append(" and quotation_goods_status in (3,4,5,6)");
        if (StringUtils.isNotBlank(kanban.getOrgName())){
            sb.append(" and vendor_organization_name like concat('%','").append(kanban.getOrgName()).append("','%') ");
        }
        sb.append(" GROUP BY DATE_FORMAT(createdTime, '%Y-%m-%d')");
        sb.append(" union all");
        sb.append(" select COUNT(1) count, 'all' time  " );
        sb.append(" from  sourcingcenter.i_sourcingcenter_t_quotation_goods ");
        sb.append(" where  deleted = 0 ");
        sb.append(" and quotation_goods_status in (3,4,5,6)");
        if (StringUtils.isNotBlank(kanban.getOrgName())){
            sb.append(" and vendor_organization_name like concat('%','").append(kanban.getOrgName()).append("','%') ");
        }


        List<KanbanDto> kanbas = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<KanbanDto>(KanbanDto.class));
        return kanbas;

    }

    public static List<KanbanDto> queryDingbiaoByTime(OperationKanbanQueryDto kanban) {

        StringBuffer sb = new StringBuffer();
        sb.append("select COUNT(1) count, DATE_FORMAT(createdTime, '%Y-%m-%d') time " );
        sb.append(" from  commoditymaterials.i_commoditymaterials_t_goods_purchase ");
        sb.append(" where  deleted=0 ");
        sb.append(" and DATE_FORMAT(createdTime, '%Y-%m-%d')>='"+kanban.getStartTime()+"'");
        sb.append(" and DATE_FORMAT(createdTime, '%Y-%m-%d')<='"+kanban.getEndTime()+"'");
        if (StringUtils.isNotBlank(kanban.getOrgName())){
            sb.append(" and vendor_name like concat('%','").append(kanban.getOrgName()).append("','%') ");
        }

        sb.append(" GROUP BY DATE_FORMAT(createdTime, '%Y-%m-%d')");
        sb.append(" union all");
        sb.append(" select COUNT(1) count, 'all' time  " );
        sb.append(" from  commoditymaterials.i_commoditymaterials_t_goods_purchase ");
        sb.append(" where  deleted=0");
        if (StringUtils.isNotBlank(kanban.getOrgName())){
            sb.append(" and vendor_name like concat('%','").append(kanban.getOrgName()).append("','%') ");
        }
        List<KanbanDto> kanbas = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<KanbanDto>(KanbanDto.class));
        return kanbas;

    }

    public static List<KanbanDto> queryOrderMoneyByTime(OperationKanbanQueryDto kanban) {
        StringBuffer sb = new StringBuffer();
        sb.append("select sum(amount) total, DATE_FORMAT(createdTime, '%Y-%m-%d') time " );
        sb.append(" from  ordercenter.i_ordercenter_t_order_customer ");
        sb.append(" where deleted=0 and after_sale ='已售后' ");
        sb.append(" and order_customer_status in ('UpcomingConfirm','Done')");
        sb.append(" and DATE_FORMAT(createdTime, '%Y-%m-%d')>='"+kanban.getStartTime()+"'");
        sb.append(" and DATE_FORMAT(createdTime, '%Y-%m-%d')<='"+kanban.getEndTime()+"'");
        if (StringUtils.isNotBlank(kanban.getOrgName())){
            sb.append(" and platform_name like concat('%','").append(kanban.getOrgName()).append("','%') ");
        }
        sb.append(" GROUP BY DATE_FORMAT(createdTime, '%Y-%m-%d')");
        sb.append(" union all");
        sb.append(" select sum(amount) total, 'all' time  " );
        sb.append(" from  ordercenter.i_ordercenter_t_order_customer ");
        sb.append(" where deleted=0 and after_sale ='已售后' ");
        sb.append("  and order_customer_status in ('UpcomingConfirm','Done') ");
        if (StringUtils.isNotBlank(kanban.getOrgName())){
            sb.append(" and platform_name like concat('%','").append(kanban.getOrgName()).append("','%') ");
        }
        List<KanbanDto> kanbas = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<KanbanDto>(KanbanDto.class));
        return kanbas;

    }


    public static List<KanbanDto>  queryOrderNumberyByTime(OperationKanbanQueryDto kanban) {
        StringBuffer sb = new StringBuffer();
        sb.append("select count(orderCustomer.id) as count,DATE_FORMAT(orderCustomer.createdTime, '%Y-%m-%d') time");
        sb.append(" from  ordercenter.i_ordercenter_t_order_customer orderCustomer");
        sb.append(" where orderCustomer.deleted=0 and orderCustomer.after_sale ='已售后' ");
        sb.append(" and orderCustomer.order_customer_status in ('UpcomingConfirm','Done') ");
        sb.append(" and DATE_FORMAT(orderCustomer.createdTime, '%Y-%m-%d')>='"+kanban.getStartTime()+"'");
        sb.append(" and DATE_FORMAT(orderCustomer.createdTime, '%Y-%m-%d')<='"+kanban.getEndTime()+"'");
        if (StringUtils.isNotBlank(kanban.getOrgName())){
            sb.append(" and platform_name like concat('%','").append(kanban.getOrgName()).append("','%') ");
        }
        sb.append(" GROUP BY DATE_FORMAT(createdTime, '%Y-%m-%d')");

        sb.append(" union all");
        sb.append(" select count(orderCustomer.id) total, 'all' time  " );
        sb.append(" from  ordercenter.i_ordercenter_t_order_customer orderCustomer");
        sb.append(" where orderCustomer.deleted=0 and orderCustomer.after_sale ='已售后' ");
        sb.append(" and orderCustomer.order_customer_status in ('UpcomingConfirm','Done') ");
        if (StringUtils.isNotBlank(kanban.getOrgName())){
            sb.append(" and platform_name like concat('%','").append(kanban.getOrgName()).append("','%') ");
        }
        List<KanbanDto> kanbas = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<KanbanDto>(KanbanDto.class));
        return kanbas;


    }

    public static Map convertParamenter(Map<Object, Object> param, List<KanbanDto> kanbas, Integer type,OperationKanbanQueryDto kanban) {

        if (kanbas != null  && kanbas.size() > 0){
            kanbas.forEach(item->{
                extractedTime(item);
            });
        }
        String startTime = kanban.getStartTime();
        String endTime = kanban.getEndTime();
        List<String> days = getDays(startTime, endTime);
        if (days != null && days.size() > 0 && kanbas != null  && kanbas.size() > 0){
            Set<String> kanbanSet = kanbas.stream().map(KanbanDto::getDayTime).collect(Collectors.toSet());
            days.forEach(day->{
               boolean flag = kanbanSet.contains(day);
               if (!flag){
                   KanbanDto kanbanDto = new KanbanDto();
                   kanbanDto.setTime(getTimeString(day));
                   kanbanDto.setDayTime(day);
                   kanbanDto.setCount("0");
                   kanbanDto.setTotal(new BigDecimal("0.0"));
                   kanbas.add(kanbanDto);
               }
           });
        }
        List<KanbanDto> list = kanbas.stream().sorted(Comparator.comparing(KanbanDto::getDayTime)).collect(Collectors.toList());
        param.put(type,list);
        return param ;

    }

    public static List<KanbanDto> queryOrderGoodNumberyByTime(OperationKanbanQueryDto kanban) {
        StringBuffer sb = new StringBuffer();
        sb.append("select");
        sb.append(" COUNT( vendor_order_good.ID ) AS count,DATE_FORMAT(vendor_order_good.createdTime, '%Y-%m-%d') time ");
        sb.append(" from ordercenter.i_ordercenter_t_order_vendor_goods  vendor_order_good");
        sb.append(" LEFT JOIN ordercenter.i_ordercenter_t_order_vendor vendor ON vendor_order_good.order_vendor_id = vendor.id and vendor.deleted = 0 ");
        sb.append(" LEFT JOIN ordercenter.i_ordercenter_t_order_customer cus ON vendor.order_customer_id = cus.id and cus.deleted = 0  ");
        sb.append(" where vendor_order_good.deleted=0 ");
        sb.append(" and DATE_FORMAT(vendor_order_good.createdTime, '%Y-%m-%d')>='"+kanban.getStartTime()+"'");
        sb.append(" and DATE_FORMAT(vendor_order_good.createdTime, '%Y-%m-%d')<='"+kanban.getEndTime()+"'");
        sb.append(" and vendor_order_good.order_vendor_goods_status in('UpcomingPayment','UpcomingConfirm','UpcomingDelivery','DoneDelivery','Done')");
        sb.append(" and (vendor.after_sale = '已售后' or vendor_order_good.quantity = returned_num)");
        if (StringUtils.isNotBlank(kanban.getOrgName())){
            sb.append(" and vendor_order_good.vendor_organization_name like concat('%','").append(kanban.getOrgName()).append("','%') ");
        }

        sb.append(" GROUP BY DATE_FORMAT(vendor_order_good.createdTime, '%Y-%m-%d')");

        sb.append(" union all");
        sb.append(" select count(vendor_order_good.id) total, 'all' time  " );
        sb.append(" from ordercenter.i_ordercenter_t_order_vendor_goods  vendor_order_good");
        sb.append(" LEFT JOIN ordercenter.i_ordercenter_t_order_vendor vendor ON vendor_order_good.order_vendor_id = vendor.id");
        sb.append(" LEFT JOIN ordercenter.i_ordercenter_t_order_customer cus ON vendor.order_customer_id = cus.id ");
        sb.append(" where vendor_order_good.deleted=0 ");
        if (StringUtils.isNotBlank(kanban.getOrgName())){
            sb.append(" and vendor_order_good.vendor_organization_name like concat('%','").append(kanban.getOrgName()).append("','%') ");
        }
        sb.append(" and DATE_FORMAT(vendor_order_good.createdTime, '%Y-%m-%d')>='"+kanban.getStartTime()+"'");
        sb.append(" and DATE_FORMAT(vendor_order_good.createdTime, '%Y-%m-%d')<='"+kanban.getEndTime()+"'");
        sb.append(" and vendor_order_good.order_vendor_goods_status in('UpcomingPayment','UpcomingConfirm','UpcomingDelivery','DoneDelivery','Done')");
        sb.append(" and (vendor.after_sale = '已售后' or vendor_order_good.quantity = returned_num)");
        List<KanbanDto> kanbas = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<KanbanDto>(KanbanDto.class));
        return kanbas;


    }

    public static List<KanbanDto> queryOrderGoodsReturn(OperationKanbanQueryDto kanban) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT");
        sb.append(" sum(r.quantity * r.price) AS total ,DATE_FORMAT(r.createdTime, '%Y-%m-%d') time ");
        sb.append(" from ");
        sb.append(" ordercenter.i_ordercenter_t_order_goods_returns r");
        sb.append(" WHERE r.deleted = 0");
        sb.append(" and DATE_FORMAT(r.createdTime, '%Y-%m-%d')>='"+kanban.getStartTime()+"'");
        sb.append(" and DATE_FORMAT(r.createdTime, '%Y-%m-%d')<='"+kanban.getEndTime()+"'");

        if (StringUtils.isNotBlank(kanban.getOrgName())){
            sb.append(" and r.vendor_organization_name like concat('%','").append(kanban.getOrgName()).append("','%') ");
        }

        sb.append(" GROUP BY DATE_FORMAT(r.createdTime, '%Y-%m-%d')");
        sb.append(" union all");
        sb.append( " SELECT");
        sb.append(" sum(r.quantity * r.price) AS total ,'all' time ");
        sb.append(" from ");
        sb.append(" ordercenter.i_ordercenter_t_order_goods_returns r");
        sb.append(" WHERE r.deleted = 0");
        sb.append(" and DATE_FORMAT(r.createdTime, '%Y-%m-%d')>='"+kanban.getStartTime()+"'");
        sb.append(" and DATE_FORMAT(r.createdTime, '%Y-%m-%d')<='"+kanban.getEndTime()+"'");
        if (StringUtils.isNotBlank(kanban.getOrgName())){
            sb.append(" and r.vendor_organization_name like concat('%','").append(kanban.getOrgName()).append("','%') ");
        }
        List<KanbanDto> kanbas = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<KanbanDto>(KanbanDto.class));
        return kanbas;
    }

    public static int queryCanyuBaojia(OperationKanbanQueryDto kanban) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT");
        sb.append(" count(1)");
        sb.append(" from ");
        sb.append(" sourcingcenter.i_sourcingcenter_t_inquiry_goods  inquiryGood");
        sb.append(" WHERE inquiryGood.deleted = 0");
        sb.append(" and (partake_count >=1   OR inquiryGood.quote_count >= 1 ) ");
        sb.append(" and DATE_FORMAT(inquiryGood.createdTime, '%Y-%m-%d')>='"+kanban.getStartTime()+"'");
        sb.append(" and DATE_FORMAT(inquiryGood.createdTime, '%Y-%m-%d')<='"+kanban.getEndTime()+"'");
        if (StringUtils.isNotBlank(kanban.getOrgName())){
            sb.append(" and inquiryGood.vendor_organization_name like concat('%','").append(kanban.getOrgName()).append("','%') ");
        }
        Integer update = jdbcTemplate.queryForObject(sb.toString(), null, Integer.class);
        return update;

    }

    public static List<Map<String,Object>> queryKanBanOrgInfo() {
        StringBuffer sb = new StringBuffer();
        sb.append("select id, name from systemmanagement.i_systemmanagement_t_organization where role_names like '%ROLE_VENDOR_ADMIN%' and deleted =0 ");
        return jdbcTemplate.queryForList(sb.toString());
    }

    public static int queryDingbiaoNum(OperationKanbanQueryDto kanban) {

        StringBuffer sb = new StringBuffer();
        sb.append("select count(1) from sourcingcenter.i_sourcingcenter_t_inquiry_goods tig");
        sb.append(" LEFT JOIN sourcingcenter.i_sourcingcenter_t_quotation_goods tqg ON tqg.inquiry_goods_id = tig.id ");
        sb.append(" LEFT JOIN sourcingcenter.i_sourcingcenter_t_inquiry  inquiry ON inquiry.id = tig.inquiry_id and inquiry.deleted=0  " );
        sb.append(" where tig.deleted = 0 and tqg.quotation_goods_status = 5 and tqg.deleted=0 " );
        sb.append(" and DATE_FORMAT(tig.modifiedTime, '%Y-%m-%d')>='"+kanban.getStartTime()+"'");
        sb.append(" and DATE_FORMAT(tig.modifiedTime, '%Y-%m-%d')<='"+kanban.getEndTime()+"'");
        if (StringUtils.isNotBlank(kanban.getOrgName())){
            sb.append(" and inquiry.organization_company_name like concat('%','").append(kanban.getOrgName()).append("','%') ");
        }
        Integer update = jdbcTemplate.queryForObject(sb.toString(), null, Integer.class);
        return update;
    }

    public static Integer queryXunjiaxiadan(OperationKanbanQueryDto kanban) {

        StringBuffer sb = new StringBuffer();
        sb.append("select count(1) from commoditymaterials.i_commoditymaterials_t_goods_purchase tgp ");
        sb.append(" LEFT JOIN sourcingcenter.i_sourcingcenter_t_quotation_goods tqg ON tqg.id = tgp.quotation_goods_id and tqg.deleted=0");
        sb.append(" LEFT JOIN sourcingcenter.i_sourcingcenter_t_inquiry_goods tig ON tig.id = tqg.inquiry_goods_id and tig.deleted=0  " );
        sb.append(" where tgp.deleted = 0 and tgp.bought <>0");
        if (StringUtils.isNotBlank(kanban.getOrgName())){
            sb.append(" and tgp.vendor_name like concat('%','").append(kanban.getOrgName()).append("','%') ");
        }
        sb.append(" and DATE_FORMAT(tgp.modifiedTime, '%Y-%m-%d')>='"+kanban.getStartTime()+"'");
        sb.append(" and DATE_FORMAT(tgp.modifiedTime, '%Y-%m-%d')<='"+kanban.getEndTime()+"'");
        Integer update = jdbcTemplate.queryForObject(sb.toString(), null, Integer.class);
        return update;
    }

    public static Integer queryQuotationInquiryNum(OperationKanbanQueryDto kanban) {
        StringBuffer sb = new StringBuffer();
        sb.append("select count(1) from sourcingcenter.i_sourcingcenter_t_inquiry_goods inquiry_goods ");
        sb.append(" LEFT JOIN sourcingcenter.i_sourcingcenter_t_inquiry inquiry   ON inquiry.id = inquiry_goods.inquiry_id and inquiry.deleted=0  " );
        sb.append(" where inquiry_goods.deleted = 0 ");
        if (StringUtils.isNotBlank(kanban.getOrgName())){
            sb.append(" and inquiry.organization_company_name like concat('%','").append(kanban.getOrgName()).append("','%') ");
        }
        sb.append(" and DATE_FORMAT(inquiry_goods.modifiedTime, '%Y-%m-%d')>='"+kanban.getStartTime()+"'");
        sb.append(" and DATE_FORMAT(inquiry_goods.modifiedTime, '%Y-%m-%d')<='"+kanban.getEndTime()+"'");
        sb.append("and inquiry_goods.inquiry_goods_status in (3,4,5) AND inquiry_goods.quote_count >= 1 ");
        Integer update = jdbcTemplate.queryForObject(sb.toString(), null, Integer.class);
        return update;


    }

}
